#程序优化
from pathlib import Path
import os
import pandas as pd
import xlwings as xw

folder_path=os.path.join(os.getcwd(),'生产日报')
folder=Path(folder_path)
file_list=folder.glob('*.xls*')
file_data=os.path.join(os.getcwd(),'生产日报分析.xlsx')
app=xw.App(visible=False,add_book=False)
#使用Pandas DataFrame来存储所有数据
all_data=[]

for i in file_list:
    workbook = app.books.open(i)
    worksheet = workbook.sheets[0]
    
    rq = worksheet.range('I2').value  # 日期
    fdl_1 = worksheet.range('B6').value  # 1#机组发电量
    fdl_2 = worksheet.range('B7').value  # 2#机组发电量
    fdl = worksheet.range('B8').value  # 当日发电量
    swdl = worksheet.range('D6').value  # 上网电量
    fscydl = worksheet.range('E8').value  # 非生产用电量
    fdcydl = worksheet.range('H8').value  # 发电厂用电量
    grcydl = worksheet.range('I8').value  # 供热厂用电量
    zhcydl = worksheet.range('J6').value/100  # 综合厂用电率
    fdcydlv = worksheet.range('K8').value/100  # 发电厂用电率
    grcydlv = worksheet.range('L8').value/100  # 供热厂用电率
    fhlv_1 = worksheet.range('M6').value/100  # 1#机组负荷率
    fhlv_2 = worksheet.range('M7').value/100  # 2#机组负荷率
    fhlv = worksheet.range('M8').value/100  # 机组负荷率
    lyxs_1 = worksheet.range('Q6').value  # 1#机组利用小时
    lyxs_2 = worksheet.range('Q7').value  # 2#机组利用小时
    lyxs = worksheet.range('Q8').value  # 机组利用小时
    yxxs_1 = worksheet.range('S6').value  # 1#机组运行小时
    yxxs_2 = worksheet.range('S7').value  # 2#机组运行小时
    yxxs = worksheet.range('S8').value  # 机组运行小时

    fdmh_1 = worksheet.range('B18').value  # 1#发电煤耗
    fdmh_2 = worksheet.range('B19').value  # 2#发电煤耗
    fdmh = worksheet.range('B20').value  # 发电煤耗
    gdmh_1 = worksheet.range('C18').value  # 1#供电煤耗
    gdmh_2 = worksheet.range('C19').value  # 2#供电煤耗
    gdmh = worksheet.range('C20').value  # 供电煤耗
    grmh_1 = worksheet.range('D18').value  # 1#供热煤耗
    grmh_2 = worksheet.range('D19').value  # 2#供热煤耗
    grmh = worksheet.range('D20').value  # 供热煤耗
    fdybm_1 = worksheet.range('E18').value  # 1#发电用标煤
    fdybm_2 = worksheet.range('E19').value  # 2#发电用标煤
    fdybm = worksheet.range('E20').value  # 发电用标煤
    grybm_1 = worksheet.range('F18').value  # 1#供热用标煤
    grybm_2 = worksheet.range('F19').value  # 2#供热用标煤
    grybm = worksheet.range('F20').value  # 供热用标煤
    fdgrbm_1 = worksheet.range('G18').value  # 1#发电供热用标煤
    fdgrbm_2 = worksheet.range('G19').value  # 2#发电供热用标煤
    fdgrbm = worksheet.range('G20').value  # 发电供热用标煤
    fdgrym_1 = worksheet.range('H18').value  # 1#发电供热用原煤
    fdgrym_2 = worksheet.range('H19').value  # 2#发电供热用原煤
    fdgrym = worksheet.range('H20').value  # 发电供热用原煤
    fdgrhy_1 = worksheet.range('I18').value  # 1#发电供热耗油
    fdgrhy_2 = worksheet.range('I19').value  # 1#发电供热耗油
    fdgrhy = worksheet.range('I20').value  # 1#发电供热耗油
    rlmrz_1 = worksheet.range('J18').value*239  # 1#机组入炉煤热值
    rlmrz_2 = worksheet.range('J19').value*239  # 2#机组入炉煤热值
    rlmrz = worksheet.range('J20').value*239 if isinstance(worksheet.range('J20').value,float) else 0  # 当日入炉煤热值
    kcml = worksheet.range('k18').value  # 库存煤量
    grl_1 = worksheet.range('l18').value  # 1#机组供热量
    grl_2 = worksheet.range('l19').value  # 2#机组供热量
    grl = worksheet.range('l20').value  # 机组供热量
    grb_1 = worksheet.range('M18').value/100  # 1#机组供热比
    grb_2 = worksheet.range('M19').value/100  # 1#机组供热比
    grb = worksheet.range('M20').value/100  # 机组供热比
    rdb_1 = worksheet.range('n18').value/100  # 1#机组热电比
    rdb_2 = worksheet.range('n19').value/100  # 2#机组热电比
    rdb = worksheet.range('n20').value/100  # 机组热电比
    grwd=worksheet.range('o18').value #供热温度
    hswd=worksheet.range('p18').value #回水温度

    yayl=worksheet.range('E57').value #液氨
    shsyl=worksheet.range('F55').value #石灰石粉
    sgcsl=worksheet.range('G55').value #石膏
    zls=worksheet.range('H55').value #自来水
    eks=worksheet.range('I55').value #二库水
    zs=worksheet.range('J55').value #中水
    ghl=worksheet.range('Q57').value #干灰量
    shl=worksheet.range('R57').value #湿灰量
    s57=worksheet.range('S57').value #渣量
    szm=worksheet.range('T57').value #石子煤量
  
    gdl_1= worksheet.range('C6').value  # 1#机组供电量
    gdl_2= worksheet.range('C7').value  # 2#机组供电量
    gdl= worksheet.range('C8').value  # 当日供电量

    yxh_1= worksheet.range('R6').value  # 1#机组运行小时
    yxh_2= worksheet.range('R7').value  # 1#机组运行小时
    yxh= worksheet.range('R8').value  # 当日运行小时

    data=[rq, fdl_1, fdl_2, fdl, swdl, fscydl, fdcydl, grcydl, zhcydl, fdcydlv, grcydlv, fhlv_1, fhlv_2, fhlv, lyxs_1, lyxs_2, lyxs, yxxs_1, yxxs_2, yxxs,
          fdmh_1,fdmh_2,fdmh,gdmh_1,gdmh_2,gdmh,grmh_1,grmh_2,grmh,fdybm_1,fdybm_2,fdybm,grybm_1,grybm_2,grybm,fdgrbm_1,fdgrbm_2,fdgrbm,fdgrym_1,fdgrym_2,
          fdgrym,fdgrhy_1,fdgrhy_2,fdgrhy,rlmrz_1,rlmrz_2,rlmrz,kcml,grl_1,grl_2,grl,grb_1,grb_2,grb,rdb_1,rdb_2,rdb,grwd,hswd,
          yayl,shsyl,sgcsl,zls,eks,zs,ghl,shl,s57,szm,gdl_1,gdl_2,gdl,yxh_1,yxh_2,yxh]
    all_data.append(data)
    
    workbook.close()
    #os.remove(i)
#用panads打开file_data
df1=pd.read_excel(file_data,sheet_name='Data')
#将列名放入columns中
columns=df1.columns  
#columns=['日期','1#机组发电量','2#机组发电量','当日发电量','上网电量','非生产用电量','发电厂用电量','供热厂用电量','综合厂用电率','发电厂用电率','供热厂用电率','1#机组负荷率','2#机组负荷率','机组负荷率','1#机组利用小时','2#机组利用小时','机组利用小时','1#机组运行小时','2#机组运行小时','机组运行小时',]
df=pd.DataFrame(all_data,columns=columns)
print(df)
#将数据写入汇总Excel文件
wk1=app.books.open(file_data)
ws1 = wk1.sheets['Data']
#找到第一个空行
first_empty_row = ws1.range('A'+str(ws1.cells.last_cell.row)).end('up').row + 1
#写入数据
ws1.range('A'+str(first_empty_row)).options(index=False,header=False).value = df
wk1.save()
wk1.close()
   
app.quit()